const express=require('express');
const mysql = require('mysql2');
const multer = require('multer'); // 引入multer

const app = express();

// 配置图片存储路径和文件名规则
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, 'public/images'); // 设置图片存储的文件夹为public/images，可根据实际需求修改
  },
  filename: function (req, file, cb) {
    const catId = req.body.cat_id; // 获取请求体中的cat_id作为图片文件名
    console.log('cat_id:', catId);
    cb(null, `${catId}`); // 仅以cat_id命名图片文件
  }
});

const upload = multer({ storage: storage });

app.use(express.json()); // 用于解析 JSON 格式的请求体
app.use(express.static('public'));

// 配置数据库信息
var connection = mysql.createConnection({
 host: 'localhost',
 port: '3306',
 user: 'root',
 password: '123456', // 你自己数据库 root 用户的密码
 database: 'web'
 })
 // 连接数据库
connection.connect();


const port = 3000;
app.listen(port,function(){
    console.log('web服务器运行于http://localhost:3000')
})


app.get('/cat',function(request,response){
  const sql = 'SELECT * FROM tb_cat';
  connection.query(sql, function(error,data){
    if(error){
      response.json({
        message:'chaxun数据库操作时遇到错误'
      }).sendStatus(500)
      console.error(error);
    }else{
    response.json({
      message:'success',
      data:data
    });
    }
  })
})

app.post('/cat/add', function(request,response){//从上课那边复制过来的response打错了打成了respoonse；
  var newcat = request.body;
  const sql='INSERT INTO tb_cat ( cat_id,breed, age, lost_data,lost_site,number,description) VALUES (?, ?, ?,?, ?, ?,?)';
  const sqlParams=[//顺序有关系吗
    newcat.cat_id,
    newcat.breed,
    newcat.age,
    newcat.lost_data,
    newcat.lost_site,
    newcat.number,
    newcat.description
  ];
  connection.query(sql,sqlParams,function(error,data){
    if(error){
      response.status(500).json({
        message:'执行数据库操作时遇到错误'
      });
      console.error(error);
    }else{
      response.json({
        message:'success'
      });
    }
  })
})


app.post('/cat/update', function(request,response){
  var newcat = request.body;
  const sql='UPDATE tb_cat SET cat_id = ?, breed = ?, age = ?, lost_data = ?, lost_site = ?, number = ?, description = ? WHERE id = ?;'
  const sqlParams=[
    newcat.cat_id,
    newcat.breed,
    newcat.age,
    newcat.lost_data,
    newcat.lost_site,
    newcat.number,
    newcat.description,
    newcat.id
  ];
  connection.query(sql,sqlParams,function(error,data){
    if(error){
      response.json({
        message:'执行数据库操作时遇到错误'
      }).sendStatus(500)
      console.error(error);
    }else{
      response.json({
        message:'success'
      });
    }
  })
})

app.get('/cat/delete',function(request,response){
  var id= request.query.id;
  //注意数据库名称别写错
  const sql = 'DELETE FROM tb_cat WHERE id = ?';
  const sqlParams=[
    id
  ];
  connection.query(sql,sqlParams,function(error,data){
    if(error){
      response.json({
        message:'执行数据库操作时遇到错误'
      }).sendStatus(500)  
      console.error(error);
    }else{
      response.json({
        message:'success'
      });
    }
  })
});

app.post('/cat/upload-picture', upload.single('picture'), function (request, response) {
  const catId = request.body.cat_id;
  
  if (!catId) {
    response.status(400).json({
      message: '缺少cat_id参数，无法为图片命名'
    });
    return;
  }
  response.json({
    message: 'success'
  });
});

// 新增的查询接口，根据cat_id查询猫咪信息
app.get('/cat/selectByCatId', function(request, response) {
  var catId = request.query.cat_id;
  // console.log(catId);
  const sql = 'SELECT * FROM tb_cat WHERE cat_id =?';
  const sqlParams = [catId];
  //测试
  console.log('Executing SQL:', sql, 'with params:', sqlParams);
  connection.query(sql, sqlParams, function(error, data) {
      if (error) {
          response.json({
              message: '查询数据库时遇到错误'
          }).sendStatus(500);
          console.error(error);
      } else {
          response.json({
              message:'success',
              data: data
          });
      }
  });
});